﻿using DAL.Comm;
using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class BasePurchaseBillDAL
    {
        private SqlCommand cmd;
        public BasePurchaseBillDAL()
        {
            cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
        }
        /// <summary>
        /// 按订单编号查询
        /// </summary>
        public List<string> FindPurchaseBillIDByPurchaseBillID(string pid)
        {
            cmd.CommandText = "FindPurchaseBillIDByPurchaseBillID";            //存储过程名
            cmd.Parameters.Clear();                                            //清空存储过程参数列表
            cmd.Parameters.Add("@PurchaseID", SqlDbType.NChar, 12).Value = pid;//添加存储过程参数
            List<string> list = new List<string>();                            //新建string泛型集合用于存储DB读出的订单集合
            SqlDataReader read = SQLHelper.ExcuteReader(cmd);                  //读库
            while (read.Read())
            {
                string id = read["PurchaseID"].ToString();//将读出的记录赋给变量id
                list.Add(id);                             //将id值添加到泛型集合中
            }
            read.Close();
            return list;
        }
        /// <summary>
        /// 根据业务员查询
        /// </summary>
        public List<string> FindPurchaseBillIDByClerk(string clerk)
        {
            cmd.CommandText = "FindPurchaseBillIDByClerk";            //存储过程名
            cmd.Parameters.Clear();                                            //清空存储过程参数列表
            cmd.Parameters.Add("@Clerk", SqlDbType.NVarChar, 6).Value = clerk;//添加存储过程参数
            List<string> list = new List<string>();                            //新建string泛型集合用于存储DB读出的订单集合
            SqlDataReader read = SQLHelper.ExcuteReader(cmd);                  //读库
            while (read.Read())
            {
                string id = read["PurchaseID"].ToString();//将读出的记录赋给变量id
                list.Add(id);                             //将id值添加到泛型集合中
            }
            read.Close();
            return list;
        }
        /// <summary>
        /// 根据审核员查询
        /// </summary>
        public List<string> FindPurchaseBillIDByExaminer(string examiner)
        {
            cmd.CommandText = "FindPurchaseBillIDByExaminer";            //存储过程名
            cmd.Parameters.Clear();                                            //清空存储过程参数列表
            cmd.Parameters.Add("@Examiner", SqlDbType.NVarChar, 6).Value = examiner;//添加存储过程参数
            List<string> list = new List<string>();                            //新建string泛型集合用于存储DB读出的订单集合
            SqlDataReader read = SQLHelper.ExcuteReader(cmd);                  //读库
            while (read.Read())
            {
                string id = read["PurchaseID"].ToString();//将读出的记录赋给变量id
                list.Add(id);                             //将id值添加到泛型集合中
            }
            read.Close();
            return list;
        }
        /// <summary>
        /// 根据订单状态查询订单
        /// </summary>
        public List<string> FindPurchaseBillIDByOnPress(int onProcess)
        {
            cmd.CommandText = "FindPurchaseIDByOnProcess";                         //设置存储过程
            cmd.Parameters.Clear();                                                //清空存储过程的参数
            cmd.Parameters.Add("@Onprocess", SqlDbType.SmallInt).Value = onProcess;//添加存储过程参数
            List<string> list = new List<string>();                                //定义类型为字符串的泛型集合list
            SqlDataReader reader = SQLHelper.ExcuteReader(cmd);//调用通用数据访问类SQLHelper读取结果集
            while (reader.Read())                              //遍历结果集reader
            {
                string pid = reader["PurchaseID"].ToString();  //将结果集中的PurchaseID值赋值给pid
                list.Add(pid);                                 //将pid值添加至泛型集合list中
            }
            reader.Close();                                    //关闭对象连接
            return list;
        }
        /// <summary>
        /// 根据订单日期查询订单
        /// </summary>
        public List<string> FindPurchaseBillIDByDate(DateTime StartDate, DateTime EndDate)
        {
            cmd.CommandText = "FindPruchAseBillByDate";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@startDate", SqlDbType.DateTime).Value = StartDate;
            cmd.Parameters.Add("@endDate", SqlDbType.DateTime).Value = EndDate;
            List<string> list = new List<string>();
            SqlDataReader reader = SQLHelper.ExcuteReader(cmd);
            while (reader.Read())
            {
                string pid = reader["PurchaseID"].ToString();
                list.Add(pid);
            }
            reader.Close();
            return list;
        }
        /// <summary>
        /// 根据订单编号查找订单（包含详单列表信息）
        /// </summary>
        public PurchaseBill FindPurchaseBillByPurchaseBillID(string pid, List<PurchaseDetail> listDetail)
        {
            cmd.CommandText = "FindPurchaseBillByPurchaseID";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@PurchaseID", SqlDbType.NChar, 12).Value = pid;//添加存储过程参数

            SqlDataReader reader = SQLHelper.ExcuteReader(cmd);
            if (reader.Read())
            {
                PurchaseBill bo = new PurchaseBill();
                bo.PurchaseBillID = reader["PurchaseID"].ToString().Trim();
                bo.SupplierID = reader["SupplierID"].ToString().Trim();
                bo.PurchaseDate = Convert.ToDateTime(reader["PurchaseDate"]);

                if (reader["StockDate"] != Convert.DBNull)//判断入库日期是否为空
                    bo.StockDate = Convert.ToDateTime(reader["StockDate"]);

                bo.Clerk = reader["Clerk"].ToString().Trim();
                //判断审核员Examiner、仓库管理员Custodian是否为空
                if(reader["Examiner"]!=Convert.DBNull || reader["Custodian"] != Convert.DBNull)
                {
                    bo.Examiner = reader["Examiner"].ToString().Trim();
                    bo.Custodian = reader["Custodian"].ToString().Trim();
                }
                bo.OnProcess = int.Parse(reader["OnProcess"].ToString());
                bo.Memo = reader["Memo"].ToString();

                bo.PurchaseDetailList = listDetail;
                reader.Close();
                return bo;
            }
            else
            {
                reader.Close();
                return null;
            }          
        }
        /// <summary>
        /// 根据订单编号查找详细订单列表信息
        /// </summary>
        public PurchaseBill FindDetailPurchaseBillByPurchaseBillID(string pid)
        {
            cmd.CommandText = "FindPurchaseBillDetailByPurchaseID";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@PurchaseID", SqlDbType.NChar, 12).Value = pid;//添加存储过程参数

            SqlDataReader reader = SQLHelper.ExcuteReader(cmd);

            List<PurchaseDetail> list = new List<PurchaseDetail>();
            while (reader.Read())
            {
                PurchaseDetail detail = new PurchaseDetail();
                detail.PurchaseID= reader["PurchaseID"].ToString().Trim();
                detail.PurchaseDetailID = Convert.ToInt16(reader["PurchaseDatailID"]);
                detail.ProductID= reader["ProductID"].ToString().Trim();
                detail.PurchasePrice= Convert.ToDecimal(reader["PurchasePrice"]);
                detail.Quantity = int.Parse(reader["Quantity"].ToString());

                list.Add(detail);
            }
            reader.Close();
            return FindPurchaseBillByPurchaseBillID(pid, list);
        }
    }
}
